package com.gxuwz.lhleavesys.dao.impl;

import com.gxuwz.lhleavesys.dao.UserDao;
import com.gxuwz.lhleavesys.db.DbUtil;
import com.gxuwz.lhleavesys.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问Dao组件
 */
@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    private DbUtil dbUtil;

    /**
     * 插入用户
     * 
     * @param user 用户对象
     */
    public void insert(User user) throws SQLException {
        String sql = "INSERT INTO sys_user(userid, username, password, role,status) VALUES (?, ?, ?, ?,?)";

        try (Connection conn = dbUtil.getConn();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int paramIndex = 1;
            pstmt.setString(paramIndex++, user.getUserid());
            pstmt.setString(paramIndex++, user.getUsername());
            pstmt.setString(paramIndex++, user.getPassword());
            pstmt.setString(paramIndex++, user.getRole());
            pstmt.setString(paramIndex, user.getStatus());
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new SQLException("执行插入数据操作发生异常：" + e.getMessage(), e);
        }
    }

    /**
     * 删除用户
     * 
     * @param id 用户ID
     * @throws SQLException 如果操作失败
     */
    public void delete(String id) throws SQLException {
        String sql = "DELETE FROM sys_user WHERE userid = ?";

        try (Connection conn = dbUtil.getConn();
                PreparedStatement pStmt = conn.prepareStatement(sql)) {

            pStmt.setString(1, id);
            pStmt.executeUpdate();
        } catch (Exception e) {
            throw new SQLException("执行删除数据操作发生异常：" + e.getMessage(), e);
        }
    }

    /**
     * 更新用户信息
     *
     * @param user 用户对象
     * @return
     */
    public boolean update(User user) throws SQLException {
        String sql = "UPDATE sys_user SET username = ?, password = ?, telNumber = ? WHERE userid = ?";

        try (Connection conn = dbUtil.getConn();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int paramIndex = 1;
            pstmt.setString(paramIndex++, user.getUsername());
            pstmt.setString(paramIndex++, user.getPassword());
//            pstmt.setString(paramIndex++, user.getRole());
//            pstmt.setString(paramIndex++, user.getStatus());
            pstmt.setString(paramIndex++, user.getTelNumber());
            pstmt.setString(paramIndex, "admin");
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new SQLException("执行更新数据操作发生异常：" + e.getMessage(), e);
        }
        return false;
    }

    /**
     * 查询用户
     * 
     * @param user 查询示例
     * @return 用户列表
     * @throws SQLException 如果操作失败
     */
    public List<User> query(User user) throws SQLException {

        List<User> list = new ArrayList<>();
        String sql = "SELECT * FROM sys_user WHERE 1=1";

        List<Object> paramsList = new ArrayList<>();
        if (user != null) {
            if (user.getUserid() != null) {
                sql += " AND userid = ?";
                paramsList.add(user.getUserid());
            }
            if (user.getPassword() != null) {
                sql += " AND password = ?";
                paramsList.add(user.getPassword());
            }

        }

        try (Connection conn = dbUtil.getConn();
                PreparedStatement pStmt = conn.prepareStatement(sql)) {

            int paramIndex = 1;
            for (Object obj : paramsList) {
                if (obj instanceof String) {
                    pStmt.setString(paramIndex++, (String) obj);
                } else if (obj instanceof Integer) {
                    pStmt.setInt(paramIndex++, (Integer) obj);
                }
                // 更多类型的参数可以在这里处理
            }

            try (ResultSet rs = pStmt.executeQuery()) {
                while (rs.next()) {
                    User users = new User();
                    users.setUserid(rs.getString("userid"));
                    users.setPassword(rs.getString("password"));
                    users.setUsername(rs.getString("username"));
                    users.setRole(rs.getString("role"));
                    users.setStatus(rs.getString("status"));
                    users.setTelNumber(rs.getString("telNumber"));
                    list.add(users);
                }
            }
            return list;
        } catch (Exception e) {
            throw new SQLException("执行查询数据操作发生异常:" + e.getMessage(), e);
        }
    }

    /*
     * 查询系统管理员所有信息
     */
    public User queryUser() throws SQLException {
        String sql = "SELECT * FROM sys_user WHERE 1=1 and userid = 'admin'";
        User user = null;
        try (Connection conn = dbUtil.getConn();
                PreparedStatement pStmt = conn.prepareStatement(sql)) {
            pStmt.executeQuery();
            try (ResultSet rs = pStmt.executeQuery()) {
                if (rs.next()) {
                    user = new User();
                    user.setUserid(rs.getString("userid"));
                    user.setPassword(rs.getString("password"));
                    user.setUsername(rs.getString("username"));
                    user.setRole(rs.getString("role"));
                    user.setRole(rs.getString("status"));
                    user.setTelNumber(rs.getString("telNumber"));

                }
            }
        } catch (Exception e) {
            throw new SQLException("执行查询数据操作发生异常:" + e.getMessage(), e);
        }
        return user;
    }

    // 构造函數，get，set方法
    public UserDaoImpl() {
    }

    public UserDaoImpl(DbUtil dbUtil) {
        this.dbUtil = dbUtil;
    }

    public DbUtil getDbUtil() {
        return dbUtil;
    }

    public void setDbUtil(DbUtil dbUtil) {
        this.dbUtil = dbUtil;
    }
}
